#!/usr/bin/python
# -*- coding: utf-8 -*-

import psycopg2
import sys
import json
import db_conf

con = None

try:
    config = db_conf.getConfig('metabvis.conf')
    con = psycopg2.connect(host=db_conf.getDBHost(config), database=db_conf.getDBName(config), port=db_conf.getDBPort(config), user=db_conf.getUser(config), password=db_conf.getPassword(config)) 
    cur = con.cursor()
    cur.execute('SELECT project_id, project_name from project order by project_name asc')
    rows = cur.fetchall()
    
    print "Content-type: text/html;charset=utf-8\r\n"
    j = []
    pa = {}
    pa['projects'] = []
    for row in rows:
	pi = []
	for r in range(len(row)):
		pi.append(row[r])
	pa['projects'].append(pi)
    cur.execute('select user_id, full_name, user_name from people order by full_name asc')
    rows = cur.fetchall()
    pa['experimenters'] = []
    for row in rows:
        pi = []
        for r in range(len(row)):
                pi.append(row[r])
        pa['experimenters'].append(pi)

    pa['strains'] = []
    cur.execute('select strain_id, organism_text, strain_name from organism o, strain st where o.organism_id = st.organism_id order by organism_text asc, strain_name ASC')
    rows = cur.fetchall()
    for row in rows:
        pi = []
        for r in range(len(row)):
                if (r == 1):
                	pi.append(row[r] + "_" + row[r+1])
                        break
                pi.append(row[r])
        pa['strains'].append(pi)
    
    #insert into bio_sample_type values (nextval('bio_sample_type_bio_sample_type_id_seq'), 'tissueA');    
    pa['bio_samples'] = []
    cur.execute('select bio_sample_type_id, bio_sample_type_text from bio_sample_type order by bio_sample_type_text asc')
    rows = cur.fetchall()
    for row in rows:
        pi = []
        for r in range(len(row)):
                pi.append(row[r])
        pa['bio_samples'].append(pi)
    pa['sample_type'] = []
    cur.execute('select sample_type_id, sample_type_text from sample_type order by sample_type_text asc')
    rows = cur.fetchall()
    for row in rows:
        pi = []
        for r in range(len(row)):
                pi.append(row[r])
        pa['sample_type'].append(pi)

    #select sample_group_id, name from sample_group where experiment_id = 3 or is_global = 't';
    pa['sample_group_global'] = []
    cur.execute("select sample_group_id, name, regex from sample_group where is_global = 't' order by name")
    rows = cur.fetchall()
    for row in rows:
        pi = []
        for r in range(len(row)):
                pi.append(row[r])
        pa['sample_group_global'].append(pi)

    #select tracer_name, mass_increment from tracer;
    pa['tracers'] = []
    cur.execute("select tracer_id, tracer_name, mass_increment from tracer order by tracer_id asc")
    rows = cur.fetchall()
    for row in rows:
        pi = []
        for r in range(len(row)):
                pi.append(row[r])
        pa['tracers'].append(pi)

    #metabobase_config (parameter varchar, value varchar)
    mconfig = {}
    cur.execute("select distinct parameter, value from metabobase_config")
    rows = cur.fetchall()
    for row in rows:
        pi = []
        for r in range(len(row)):
                pi.append(row[r])
        mconfig[row[0]] = row[1]
    pa["config"] = mconfig
    print json.dumps(pa)
except psycopg2.DatabaseError, e:
    print 'Error %s' % e    
    sys.exit(1)    
finally:
    if con:
        con.close()
